''' Module provides utility  / helper function on sqlalchemy CORE framework.

All generated clases share following arguments:

metadata:
    Metadata to create clause for.

columns:
    List of column names to retrieve.
    columns=['TABLE_NAME_1.COL_1',
             'TABLE_NAME_1.COL_2',
            ]
pk_value:
    value of primary key column. Up until now only single column PK is
    supported.

where:
    Where part of query clause.
    Up until now just one form is supported.

    Where parameter is list of conditions connected by AND. Each condition
    is list where first item is left part of AND, and second item is right
    part of AND.
    If list contains more than two items, each is connected by OR clause,
    within AND clause. They are alternative values to compare.
    Right part can be of simple datatype, or callable. If right part is
    simple datatype left and right part are compared to equality.

    Example:
    where=[['TABLE_NAME_1.COL_1', 'A', 'B', like('%ABC')],
           ['TABLE_NAME_1.COL_2'], eq(2), le(3), ge(4)]

    Will generate:
    where (   TABLE_NAME_1.COL_1 = 'A'
           or TABLE_NAME_1.COL_1 = 'B'
           or TABLE_NAME_1.COL_1 like '%ABC')
    and (   TABLE_NAME_1.COL_2 = 2
         or TABLE_NAME_1.COL_2 <= 3,
         or TABLE_NAME_1.COL_2 >= 4)

order:
    Order part of query clause.
    List of columns and sort orders preceeding with +/- sign.

    order=['+TABLE_NAME_1.COL_1', '-TABLE_NAME_1.COL_2']

mapping:
    Dictionary of column name aliases. If given, mapping is applied to all
    column names. If given affected paramters are:
    columns, where, order.

    mapping={'col11': 'TABLE_NAME_A.COL_1',
             'col23': 'TABLE_NAME_B.COL_3'}

values:
    Dictionary of values to insert.
    values= {'TABLE_NAME_1.COL_1',: 1, 'TABLE_NAME_1.COL_2': 'some name'}

'''
from __future__ import absolute_import

from functools import partial
from sqlalchemy import Table
from sqlalchemy import and_, or_
from sqlalchemy import asc, desc
from sqlalchemy import select


##### Batch insert ############################################################
def insert_fn(connection, metadata):
    ''' Partial version of insert_data function

    Example:
    insert = insert_fn(connection, metadata)
    insert('table_name', [['col_name_1', 'col_name_2'],
                          [val_1_1, val_1_2],
                          [val_2_1, val_2_2]])
    '''
    return partial(insert_data, connection, metadata)


def insert_data(connection, metadata, table_name, data):
    ''' Function executes batch insert on given connection

    Arguments:
    connection:
        Connection insert is executed on.

    table_name:
        Name of table where data will be inserted.

    data:
        [
            [colname_1, colname_2, ..., colname_x],
            [val_1_1], [val_1_2], ..., [val_1_x],
            [val_2_1], [val_2_2], ..., [val_2_x],
            ...
        ]
    '''
    table = Table(table_name, metadata)
    connection.execute(table.insert(), normalize_insert_batch(data))


def normalize_insert_batch(data):
    ''' Helper function to convert data into sqlalchemy batch insert format

    Arguments:
    data:
        [
            [colname_1, colname_2, ..., colname_x],
            [val_1_1], [val_1_2], ..., [val_1_x],
            [val_2_1], [val_2_2], ..., [val_2_x],
            ...
        ]

    Returns:
    [
       [{colname_1: val_1_1}, {colname_2: val_1_2}, ..., {colname_3: val_1_x}],
       [{colname_1: val_2_1}, {colname_2: val_2_2}, ..., {colname_3: val_2_x}],

     ]
    '''
    return [dict(zip(data[0], row)) for row in data[1:]]


##### Generic sql functions ###################################################
def query(metadata, tables, columns=None, where=None, order=None,
          mapping=None):
    ''' Function creates sqlalchemy select clause

    Arguments:
    tables:
        FROM part of select clause. Parameter can have one of three
        forms.

        1. String:
            tables='TABLE_NAME'

        2. List of strings:
            tables=['TABLE_NAME_1', 'TABLE_NAME_2', ...]

            Tables are default joined by left outer join

        3. Callable:
            tables=left_outer_join(*table_names)
            Defines type of join between tables.

        If table name takes form of simple string, resulting executed query,
        will contain simple column names.
        If list or callable is passed, column names will be prefixed by
        table name and underscore in resulting query.

    RETURNS:
        sqlalchemy query clause
    '''
    if isinstance(tables, list):
        table_source = left_outer_join(*tables)(metadata)
    elif hasattr(tables, '__call__'):
        table_source = tables(metadata)
    else:
        table_source = Table(tables, metadata)

    use_labels = hasattr(tables, '__call__') or isinstance(tables, list)

    if columns is not None:
        select_command = select(_columns(metadata, columns, mapping),
                                from_obj=table_source,
                                use_labels=use_labels)
    else:
        select_command = table_source.select(use_labels=use_labels)

    if where is not None:
        where_cond = where_clause(metadata, where, mapping)
        select_command = select_command.where(where_cond)

    if order is not None:
        order_by = [_order(order_clause[0])(_column(metadata,
                                                    order_clause[1:],
                                                    mapping))
                    for order_clause in order]
        select_command = select_command.order_by(*order_by)
    return select_command


def query_by_pk(metadata, table_name, pk_value, columns=None, mapping=None):
    ''' Function creates sqlalchemy select clause by PK

    Arguments:
    tables:
        FROM part of select clause. Parameter can have one of three
        forms.

        1. String:
            tables='TABLE_NAME'

        2. List of strings:
            tables=['TABLE_NAME_1', 'TABLE_NAME_2', ...]

            Tables are default joined by left outer join

        3. Callable:
            tables=left_outer_join(*table_names)
            Defines type of join between tables.

        If table name takes form of simple string, resulting executed query,
        will contain simple column names.
        If list or callable is passed, column names will be prefixed by
        table name and underscore in resulting query.

    RETURNS:
        sqlalchemy query clause
    '''
    table = Table(table_name, metadata)
    pk_c_name = _pk_column_name(table)

    if mapping is not None and columns is not None:
        columns = [mapping[column] for column in columns]

    column_full_name = table_name + '.' + pk_c_name
    where = [[column_full_name, pk_value]]
    select_command = query(metadata, table_name, columns=columns, where=where)
    return select_command


def insert(metadata, table_name, values, mapping=None):
    ''' Function creates sqlalchemy insert clause

    Arguments:
    table_name:
        Name of table in insert clause. Accepts simple string argument.

    RETURNS:
        sqlalchemy insert clause
    '''
    table = Table(table_name, metadata)
    values = _map_values(table_name, values, mapping)
    insert_command = table.insert().values(**values)
    return insert_command


def update(metadata, table_name, values, where=None, mapping=None):
    ''' Function creates sqlalchemy update clause

    Arguments:
    table_name:
        Name of table in update clause. Accepts simple string argument.

    RETURNS:
        sqlalchemy update clause
    '''
    table = Table(table_name, metadata)
    values = _map_values(table_name, values, mapping)
    update_command = table.update()
    if where is not None:
        where_cond = where_clause(metadata, where, mapping)
        update_command = update_command.where(where_cond)

    update_command = update_command.values(**values)
    return update_command


def update_by_pk(metadata, table_name, pk_value, values, mapping=None):
    ''' Function creates sqlalchemy update clause by PK

    Arguments:
    table_name:
        Name of table in update clause. Accepts simple string argument.

    RETURNS:
        sqlalchemy update clause
    '''
    table = Table(table_name, metadata)
    pk_c_name = _pk_column_name(table)
    values = _map_values(table_name, values, mapping)
    update_command = table.update().\
        where(table.columns[pk_c_name] == pk_value).\
        values(**values)
    return update_command


def delete(metadata, table_name, where=None, mapping=None):
    ''' Function creates sqlalchemy delete clause

    Arguments:
    table_name:
        Name of table in delete clause. Accepts simple string argument.

    RETURNS:
        sqlalchemy delete clause
    '''
    table = Table(table_name, metadata)
    delete_command = table.delete()
    if where is not None:
        where_cond = where_clause(metadata, where, mapping)
        delete_command = delete_command.where(where_cond)
    return delete_command


def delete_by_pk(metadata, table_name, pk_value):
    ''' Function creates sqlalchemy delete clause by PK

    Arguments:
    table_name:
        Name of table in delete clause. Accepts simple string argument.

    RETURNS:
        sqlalchemy delete clause
    '''
    table = Table(table_name, metadata)
    pk_c_name = _pk_column_name(table)
    delete_command = table.delete().\
        where(table.columns[pk_c_name] == pk_value)
    return delete_command


def _map_values(table_name, values, mapping):
    if mapping is None:
        return values

    expanded_data = {}
    for k, v in values.items():
        mapped_name = mapping[k]
        mapped_table_name, mapped_column_name = mapped_name.split('.')
        if mapped_table_name == table_name:
            expanded_data[mapped_column_name] = v

    return expanded_data


def _pk_column_name(table):
    # Yet we support just one PK column. Add support for more if needed.
    pk_col_names = [col.name for col in table.primary_key.columns]
    return pk_col_names[0]


def where_clause(metadata, where, mapping):
    and_clause = [_parse_where_clause(metadata, clause, mapping)
                 for clause in where]
    return and_(*and_clause)


def _parse_where_clause(metadata, and_where, mapping):
    or_clause = [_cond(_column(metadata, and_where[0], mapping), value)
                 for value in _clause_values(and_where)]
    return or_(*or_clause)


def _cond(column, value):
    if hasattr(value, '__call__'):
        return value(column)
    else:
        return column == value


def _columns(metadata, columns_full_name, mapping):
    return [_column(metadata, column_full_name, mapping)
            for column_full_name in columns_full_name]


def _column(metadata, column_full_name, mapping):
    if mapping is not None:
        column_full_name = mapping[column_full_name]
    table_name, column_name = column_full_name.split('.')
    table = Table(table_name, metadata)
    return table.columns[column_name]


def _clause_values(clause):
    if isinstance(clause[1], list):
        return clause[1][:]
    else:
        return clause[1:]


def eq(value):
    def fn(column):
        return column == value
    return fn


def gt(value):
    def fn(column):
        return column > value
    return fn


def lt(value):
    def fn(column):
        return column < value
    return fn


def le(value):
    def fn(column):
        return column <= value
    return fn


def ge(value):
    def fn(column):
        return column >= value
    return fn


def like(value):
    def fn(column):
        return column.like(value.replace('*', '%'))
    return fn


def none():
    def fn(column):
        return column == None
    return fn


def not_none():
    def fn(column):
        return column != None
    return fn


def left_outer_join(*table_names):
    def fn(metadata):
        tables = [Table(table_name, metadata)
                  for table_name in table_names]
        return reduce(lambda t1, t2: t1.outerjoin(t2), tables)
    return fn


def _order(sign):
    if sign not in ('+', '-'):
        raise TypeError('Only + or - signs are allowed. Found: {0}'.\
                        format(sign))

    return asc if sign == '+' else desc
